import java.sql.*;

/**
 * 从数据库读写学生信息
 */
public class StudentDatabaseRepo implements StudentRepo {
    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void newStudent(Student student) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:sqlite:sqlite");
        Statement statement = connection.createStatement();
        PreparedStatement ps = connection.prepareStatement("insert into student (stuNum,className,name,sex,age) values (?,?,?,?,?)");
        ps.setString(1, student.getStuNum());
        ps.setString(2, student.getClassName());
        ps.setString(3, student.getName());
        ps.setString(4, student.getSex());
        ps.setInt(5, student.getAge());
        ps.execute();
        ps.close();
        connection.close();
    }

    @Override
    public void deleteStudent(String stuNum) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:sqlite:sqlite");
        PreparedStatement ps = connection.prepareStatement("delete from student where stuNum=?");
        ps.setString(1, stuNum);
        ps.execute();
        ps.close();
        connection.close();
    }

    @Override
    public void updateStudent(Student student) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:sqlite:sqlite");
        PreparedStatement ps = connection.prepareStatement("update student set name=?,age=?,sex=?,className=? where stuNum=?");
        ps.setString(1, student.getName());
        ps.setInt(2, student.getAge());
        ps.setString(3, student.getSex());
        ps.setString(4, student.getClassName());
        ps.setString(5, student.getStuNum());
        ps.execute();
        ps.close();
        connection.close();
    }

    @Override
    public Student findStudent(String stuNum) throws SQLException {
        // 链接数据库
        Connection connection = DriverManager.getConnection("jdbc:sqlite:sqlite");
        // 定义查询语句
        PreparedStatement ps = connection.prepareStatement("select * from student where stuNum=?");
        ps.setString(1, stuNum);
        // 执行查询语句 并得到查询的结果
        ResultSet resultSet = ps.executeQuery();
        // 将查询的结果放到Student对象中
        Student student = new Student();
        student.setStuNum(resultSet.getString("stuNum"));
        student.setClassName(resultSet.getString("className"));
        student.setName(resultSet.getString("name"));
        student.setSex(resultSet.getString("sex"));
        student.setAge(resultSet.getInt("age"));
        // 关闭资源
        resultSet.close();
        ps.close();
        connection.close();
        //返回查询的结果
        return student;
    }
}
